package Database;

import Beans.Member;
import Beans.Movie;

import java.math.BigInteger;
import java.security.SecureRandom;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.naming.NamingException;

import Security.Encoding;

public class DatabaseInterface {
	
	private Connection conn;
	static ResultSet rs;
	private PreparedStatement stmt;
	private PreparedStatement prepStmt;

	public DatabaseInterface() {
		try {
			conn = DataBaseConnectionPool.getDBConnection();
		} 
		catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
//	public DatabaseInterface() {
//		try {
//			Class.forName("com.mysql.jdbc.Driver").newInstance();
//			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "mshen", "");
//			
//			if (!conn.isClosed())
//				System.out.println("Successfully Connected!");
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
//	}
	
	public Boolean emailExists(String email) {
		ResultSet rs;
		
		try {
			stmt = conn.prepareStatement("SELECT memNum FROM Membership WHERE email = ?");
			stmt.setString(1, email);
			rs = stmt.executeQuery();

			if(rs.next()) {
				return true;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public String signIn(String email, String password) {
		String result = "";
		ResultSet rs;
		
		try {
			//String query = "SELECT password, passwordsalt FROM User WHERE email='" + email + "'";
			stmt = conn.prepareStatement("SELECT email, password, salt FROM Membership WHERE email = ?");
			stmt.setString(1, email);
			//rs = stmt.executeQuery(query);
			rs = stmt.executeQuery();
			
			if(rs.next()) {
				//System.out.println(rs.getString("email"));
				String hashAndSaltedInputPW = Encoding.hashAndSalt(password, "123");
				
				System.out.println("Raw Password: " + password);
				String storedPW = rs.getString("password").trim();
				
				if(storedPW.equals(hashAndSaltedInputPW)) {
					result = "true" + "$";
					System.out.println("(signIn): Sign-In Successful");
				}
				else {
					System.out.println("Stored Salt: " + rs.getString("salt"));
					System.out.println("Generated: " + hashAndSaltedInputPW);
					System.out.println("Stored: " + storedPW);
					result = "false$Invalid Password.";
				}
			} else {
				result = "false$User Does Not Exist. Please ensure you have the correct username and are registered";
				System.out.println("no rs.next");
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public Integer createAccount(Member member_info){
		System.out.println("Inside CreateAccount");
		
		if (emailExists(member_info.getEmail()))
			return 0;
		else
			try {
				prepStmt = conn.prepareStatement("INSERT INTO Person (email, firstName, lastName, address, city, state, zip) " +
						 "VALUES (?, ?, ?, ?, ?, ?, ?)");

				prepStmt.setString(1, member_info.getEmail());
				prepStmt.setString(2, member_info.getFirst());
				prepStmt.setString(3, member_info.getLast());
				prepStmt.setString(4, member_info.getAddress());
				prepStmt.setString(5, member_info.getCity());
				prepStmt.setString(6, member_info.getState());
				prepStmt.setInt(7, member_info.getZip());
				
				if (prepStmt.executeUpdate() == 0)
					return 0;
				
				SecureRandom random = new SecureRandom();
				String salt = new BigInteger(130, random).toString(32);
				String hashedPW = Encoding.hashAndSalt(member_info.getPassword(), "123");
				String hashedPW2 = Encoding.hashAndSalt(member_info.getPassword(), "123");
				
				System.out.println("Raw Password:" + member_info.getPassword());
				System.out.println("Generated Salt: " + salt);
		
				prepStmt = conn.prepareStatement("INSERT INTO Membership (email, password, salt, outstandingMovies, fees, type)" +
											 "VALUES (?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
				

				
				System.out.println("Hashed Password: " + hashedPW);
				System.out.println("Hashed Password2: " + hashedPW2);
				
				
				prepStmt.setString(1, member_info.getEmail());
				prepStmt.setString(2, hashedPW);
				prepStmt.setString(3, salt);
				prepStmt.setDouble(4, 0);
				prepStmt.setDouble(5, 0);
				prepStmt.setString(6, member_info.getType());

				if (prepStmt.executeUpdate() == 1) {
					rs = prepStmt.getGeneratedKeys();
					
					if (rs.first())
						return rs.getInt(1);
					else
						return 0;
				}
			} catch (SQLException E) {
				E.printStackTrace();
			}

			return 0;
//				if (rs.first()) {
//					String storedEmail = rs.getString("email");
//					Integer memNum = rs.getInt("memNum");
//					
//					if (memNum == null) {
//						String insertQuery = "INSERT INTO Membership (type, fees, outstandingMovies, email) VALUES (?, ?, ?, ?)";
//						prepStmt = conn.prepareStatement(insertQuery, Statement.RETURN_GENERATED_KEYS);
//						
//						if (type.equals("premium"))
//							prepStmt.setString(1, "premium");
//						else
//							prepStmt.setString(1, "basic");
//						
//						prepStmt.setDouble(2, 0);
//						prepStmt.setInt(3, 0);
//						prepStmt.setString(4, storedEmail);
//						prepStmt.executeUpdate();
//						
//						rs = prepStmt.getGeneratedKeys();
//		
//						if (rs.first()) {
//							conn.commit();
//							return rs.getInt(1);
//						}
//					}
//					else
//						return 0;
//				} else {
//					String insertPerson = "INSERT INTO Person (email, first, last, address, city, state, zip) VALUES (?, ?, ?, ?, ?, ?, ?)";
//					
//					prepStmt = conn.prepareStatement(insertPerson);
//					prepStmt.setString(1, email);
//					prepStmt.setString(2, member_info.getFirst());
//					prepStmt.setString(3, member_info.getLast());
//					prepStmt.setString(4, member_info.getAddress());
//					prepStmt.setString(5, member_info.getCity());
//					prepStmt.setString(5, member_info.getState());
//					prepStmt.setInt(5, member_info.getZip());
		
//					if (prepStmt.executeUpdate() == 1) {
//						String insertMembership = "INSERT INTO Membership (type, fees, outstandingMovies, email) VALUES (?, ?, ?, ?)";
//					
//						prepStmt = conn.prepareStatement(insertMembership, Statement.RETURN_GENERATED_KEYS);
						
//						if (type.equals("premium"))
//							prepStmt.setString(1, "premium");
//						else
//							prepStmt.setString(1, "basic");
//						
//						prepStmt.setDouble(2, 0);
//						prepStmt.setInt(3, 0);
//						prepStmt.setString(4, email);
//						
//						if (prepStmt.executeUpdate() == 1) {
//							rs = prepStmt.getGeneratedKeys();
//							
//							if (rs.first())
//								return rs.getInt(1);
//							else
//								return 0;
//						}
//					} else
//						return 0;
//		
//				}
//			}
//		return 0;
	}

	public boolean deleteExistingMember(String email) throws SQLException {
		System.out.println("Inside deleteExistingPremiumMember");
		
		String emailQuery = "SELECT Person.email, Membership.memNum FROM Person LEFT JOIN Membership ON Person.email=Membership.email WHERE Person.email=?";
		prepStmt = conn.prepareStatement(emailQuery);

		prepStmt.setString(1, email);

		rs = prepStmt.executeQuery();
	
		if (rs.first()) {
			Integer memNum = rs.getInt(2);
			
			if (memNum == null)
				return false;
			else {
				String deleteMember = "DELETE FROM Membership WHERE email=?";
				
				prepStmt = conn.prepareStatement(deleteMember);
				prepStmt.setString(1, email);
				
				if (prepStmt.executeUpdate() == 0)
					return false;
				
				deleteMember = "DELETE FROM Person WHERE email=?";
				
				prepStmt = conn.prepareStatement(deleteMember);
				prepStmt.setString(1, email);
				
				if (prepStmt.executeUpdate() == 1)
					return true;
				else
					return false;
			}
		}
		
		return false;
	}
	
	
	public Integer createMovie(Movie movie) throws SQLException {
		System.out.println("Inside createMovie");
		

		String insertMovie = "INSERT INTO Movies (title, banner, releaseDate, rentalFee, category, quantity) VALUES (?, ?, ?, ?, ?, ?)";
		prepStmt = conn.prepareStatement(insertMovie, Statement.RETURN_GENERATED_KEYS);

		prepStmt.setString(1, movie.getTitle());
		prepStmt.setString(2, movie.getBanner());
		System.out.println("Inside createMovie");
		prepStmt.setDate(3, new java.sql.Date(0));
		prepStmt.setDouble(4, movie.getRentalFee());
		prepStmt.setString(5, movie.getCategory());
		prepStmt.setInt(6, movie.getQuantity());

		
		if (prepStmt.executeUpdate() == 1) {
			rs = prepStmt.getGeneratedKeys();
			System.out.println("Inside createMovie");
			if (rs.first())
				return rs.getInt(1);
			else
				return 0;
		}

		return 0;
	}
	
	public boolean deleteMovie(Integer movie_id) {
		System.out.println("Inside deleteMovie");
		
		try {
			String deleteMovie = "DELETE FROM Movies WHERE movieID=?";
			prepStmt = conn.prepareStatement(deleteMovie);
	
			prepStmt.setInt(1, movie_id);
	
			if (prepStmt.executeUpdate() == 1)
				return true;
			else
				return false;
			
		} catch (SQLException E) {
			System.err.println("SQLExcpeption: " + E.getMessage());
			return false;
		}
	}
	
	
	
	public boolean rentMovie(String email, Integer movieId) {
		System.out.println("Inside databaseInterfae rentMovie");
		int rowcount = 0;
		String updateQ="";
		int newQuantity = 0;
		
		try {
			 conn.setAutoCommit(false);
			 
			 String selectQ = "SELECT quantity FROM Movies WHERE movieID = ? "; 
			 prepStmt = conn.prepareStatement(selectQ);
			 prepStmt.setInt(1, movieId);
			 ResultSet rs = prepStmt.executeQuery();
			 
			 while (rs.next()) {
				 newQuantity = rs.getInt("quantity");
			 }
			 
			 if (newQuantity>0) {
				 String query = "INSERT INTO Rental (movieID, email, status) VALUES (?, ?, ?)";
				 
				 prepStmt = conn.prepareStatement(query);
				 prepStmt.setInt(1, movieId);
				 prepStmt.setString(2, email);
				 prepStmt.setString(3, "BORROW");
				 rowcount =  prepStmt.executeUpdate();
				 
				 if (rowcount>0) {
					 //updateQ = "UPDATE ITEM SET QUANTITY = ? , LAST_UPDATED_DATE = ?  WHERE ITEM_ID =? ";
					 updateQ = "UPDATE Movies SET quantity = ? WHERE movieID =? ";
					 prepStmt = conn.prepareStatement(updateQ);
					 prepStmt.setInt(1, (newQuantity-1));
					 //prepStmt.setTimestamp(2, getCurrentDateTime());
					 prepStmt.setInt(2, movieId);
					 rowcount =  prepStmt.executeUpdate();
					 System.out.println("Rent movie Succeed");
				 }
				 else {
					 return false;
				 }
				 conn.commit();
			 }
			 else {
				 return false;
			 }
			 
		}
		catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
			
		}
		finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return true;
	}
	
	
	/**
	 * 
	 * @param memberId
	 * @param movieId
	 * @return
	 */
	public boolean returnMovie(String email, Integer movieId) {
		int rowcount = 0;
		String updateQ="";
		int existingQuantity = 0;
		int rentedId = 0;
		try {
			 conn.setAutoCommit(false);
			 
			 String selectQ = "SELECT quantity FROM Movies WHERE movieID = ? "; 
			 prepStmt = conn.prepareStatement(selectQ);
			 prepStmt.setInt(1, movieId);
			 rs = prepStmt.executeQuery();
			 while (rs.next()) {
				 existingQuantity = rs.getInt("quantity");
			 }
			 
			 selectQ = "SELECT rentedID FROM Rental WHERE movieID = ? and email = ? "; 
			 prepStmt = conn.prepareStatement(selectQ);
			 prepStmt.setInt(1, movieId);
			 prepStmt.setString(2, email);
			 rs = prepStmt.executeQuery();
			 while (rs.next()) {
				 rentedId = rs.getInt("rentedID");
			 }
			 
			 //updateQ = "UPDATE ITEM SET QUANTITY = ? , LAST_UPDATED_DATE = ?  WHERE ITEM_ID =? ";
			 updateQ = "UPDATE Movies SET quantity = ? WHERE movieID =? ";
			 prepStmt = conn.prepareStatement(updateQ);
			 prepStmt.setInt(1, (existingQuantity+1));
			 //prepStmt.setTimestamp(2, getCurrentDateTime());
			 prepStmt.setInt(2, movieId);
			 rowcount =  prepStmt.executeUpdate();
			 
			 updateQ = "UPDATE Rental SET status = ? WHERE rentedID = ? ";
			 prepStmt = conn.prepareStatement(updateQ);
			 prepStmt.setString(1, "RETURN");
			 //prepStmt.setTimestamp(2, getCurrentDateTime());
			 prepStmt.setInt(2, rentedId);
			 rowcount =  prepStmt.executeUpdate();
			 
			 conn.commit();
			 System.out.println("Return movie Succeed");

		}
		catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				 System.out.println("Return movie fails");
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
			return false;
			
		}
		finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return true;
	}
	
	
	
	/**
	 * 
	 * @param member
	 * @return
	 */
	public Integer updateMemberInformation(Member member) {
		System.out.println("Inside updateMemberInformation");
		String result = "";
		int rowcount =0;
		try {
			 String query = "UPDATE Person SET firstName = ?, lastName = ?, address = ?, " +
			 		" city = ?, state = ?, zip = ? WHERE email = ?";
			 prepStmt = conn.prepareStatement(query);
			 prepStmt.setString(1, member.getFirst());
			 prepStmt.setString(2, member.getLast());
			 prepStmt.setString(3, member.getAddress());
			 prepStmt.setString(4, member.getCity());
			 prepStmt.setString(5, member.getState());
			 prepStmt.setInt(6, member.getZip());
			 prepStmt.setString(7, member.getEmail());
			 rowcount = prepStmt.executeUpdate();
			 
			 if (rowcount>0) {
				 query = "UPDATE Membership SET type = ?, fees = ?, outstandingMovies = ? " +
					 		" WHERE email = ?";
				 prepStmt.setString(1, member.getType());
				 prepStmt.setDouble(2, member.getFees());
				 prepStmt.setInt(3, member.getOutstandingMovies());
				 prepStmt.setInt(4, member.getMemNum());
				 rowcount = prepStmt.executeUpdate();
				 
				 if (rowcount>0) {
					 result = "true";
					 System.out.println("updateMemberInformation Succeed");
				 }
				 else {
					 result = "updateMemberInformation Failed";
				 }
			 }
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		return rowcount;
	}
	

    /**
     * 
     * @param movie
     * @return
     */

	public String updateMovieInformation(Movie movie) {
		System.out.println("Inside updateMovieInformation");
		String result = null;
		int rowcount =0;
	
		Boolean [] selectedAttributes = new Boolean[6];
		int trueCount = 0;
		
		String[] columns = new String[6];
		
		for (int i=0; i<selectedAttributes.length; i++) {
			selectedAttributes[i] = false;
			columns[i] = " ";
		}
		
		try {
			 conn.setAutoCommit(false);
			 //String query = "UPDATE Movie SET title = ?, banner= ?, rentalFee = ?, " +
			 //		" category = ?, quantity = ?, releaseDate = ? WHERE movieID = ?";
			
			 if (movie.getTitle()!=null && !movie.getTitle().isEmpty()) {
				 selectedAttributes[0] = true;
				 trueCount ++;
				 columns[0] = "title = ? ";
			 }
			 
			 if (movie.getBanner()!=null && !movie.getBanner().isEmpty()) {
				 selectedAttributes[1] = true;
				 trueCount ++;
				 columns[1] = "banner = ? ";
			 }
			 
			 if (movie.getCategory()!=null && !movie.getCategory().isEmpty()) {
				 selectedAttributes[2] = true;
				 trueCount ++;
				 columns[2] = "category = ? ";
			 }
			 
			 if (movie.getDate()!=null && !movie.getDate().isEmpty()) {
				 selectedAttributes[3] = true;
				 trueCount ++;
				 columns[3] = "releaseDate = ? ";
			 }
			 
			 if (movie.getQuantity() >= 0) {
				 selectedAttributes[4] = true;
				 trueCount ++;
				 columns[4] = "quantity = ? ";
			 }
			 
			 if (movie.getRentalFee() >= 0) {
				 selectedAttributes[5] = true;
				 trueCount ++;
				 columns[5] = "rentalFee = ? ";
			 }
			 
			 
			 String sqlQuery = buildUpdateSql(selectedAttributes, trueCount, columns);
			 
			 			 
			 prepStmt = conn.prepareStatement(sqlQuery);
			 setPreParedStatmentForMovieUpdate(prepStmt, selectedAttributes, movie);
			 
			 /*prepStmt.setString(1, movie.getTitle());
			 prepStmt.setString(2, movie.getBanner());
			 prepStmt.setDouble(3, movie.getRentalFee());
			 prepStmt.setString(4, movie.getCategory());
			 prepStmt.setInt(5, movie.getQuantity());
			 prepStmt.setTimestamp(6, getCurrentDateTime());*/
			 
			 prepStmt.setInt(trueCount+1, movie.getMovieID());
			 rowcount = prepStmt.executeUpdate();
			 
			 if (rowcount>0) {
				 result = "true";
				 System.out.println("updateMovieInformation Succeed");
			 }
			 else {
				 result = "updateMovieInformation Failed";
			 }
			 conn.commit();
		}
		catch (SQLException e) {
			 try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally{
			 try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return result;
	}
	
	
	
	public String buildUpdateSql(Boolean[] selectedAttributes, int trueCount, String[] columns) {
		String updateQ = null;
		StringBuilder sb = new StringBuilder();
		String str = "UPDATE Movie SET  ";
		sb.append(str);
		
		for (int i =0; i<=trueCount; i++) {
			if (selectedAttributes[i] && i<=(trueCount-1) ) {
				sb.append(columns[i]);
				sb.append(", ");
			}
			else if (selectedAttributes[i]) {
				sb.append(columns[i]);
			} 
		}
		
		sb.append(" WHERE movieID = ? ");
		updateQ = sb.toString();
		return updateQ;
		
	}
	
	
	public void setPreParedStatmentForMovieUpdate(PreparedStatement prepStmt, Boolean [] selectedAttributes, Movie movie_info ) throws SQLException{
		
		 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");
         java.util.Date date = null;
		 try {
				date = sdf.parse(movie_info.getDate());
		 } 
		 catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
		 }
        
         java.sql.Date sqlDate = new Date(date.getTime());
        
         //System.out.println("String converted to java.sql.Date :" + sqlDate);
         
         
		int witchQuestionMark = 0;
		for(int i=0; i < selectedAttributes.length; i++)
		{
			if(selectedAttributes[i])
			{
				witchQuestionMark++;
			    if(i == 0)
			    	prepStmt.setString(witchQuestionMark, movie_info.getTitle());
			    else if(i == 1)
			    	prepStmt.setString(witchQuestionMark, movie_info.getBanner());
			    else if(i==2)
			    	prepStmt.setString(witchQuestionMark, movie_info.getCategory());
			    else if(i==3)
			    	prepStmt.setDate(witchQuestionMark, sqlDate);
			    else if(i==4)
			    	prepStmt.setInt(witchQuestionMark, movie_info.getQuantity());
			    else
			    	prepStmt.setDouble(witchQuestionMark, movie_info.getRentalFee());
			}
		}
	}
	
	
	public String getMembershipType(String email) {
		String result = null;
		
		try {
			stmt = conn.prepareStatement("SELECT type, fees " +
										 "FROM Membership " +
										 "WHERE email = ?");
			stmt.setString(1, email);
			rs = stmt.executeQuery();
			
			if(rs.next()) {
				result = "true$" + rs.getString("type") + "$" + rs.getString("fees");
			} else {
				result = "false$" + "Error getting membership type.";
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public Movie[] getMovieIdsBorrowedByUser(String email) {
		ArrayList<Movie> result = new ArrayList<Movie>();
		
		try {
			stmt = conn.prepareStatement("SELECT Movie.movieID, Movie.title, Movie.category" +
										 "FROM Rental, Movie" +
										 "WHERE Rental.email = ? AND" +
										 "Rental.status = ? AND" +
										 "Rental.movieID = Movie.movieID");
			stmt.setString(1, email);
			stmt.setString(2,  "BORROW");
			rs = stmt.executeQuery();
			if(rs.next()) {
				Movie movie_obj = new Movie();
				movie_obj.setMovieID(rs.getInt("movieID"));
				movie_obj.setTitle(rs.getString("title"));
				movie_obj.setCategory(rs.getString("category"));
				result.add(movie_obj);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		Movie[] movies = new Movie[result.size()];
		for(int i = 0 ; i < result.size() ; i++) {
			movies[i] = result.get(i);
		}
		
		return movies;
	}
	
	
	/**
	 * 
	 * @return
	 */
	public static java.sql.Timestamp getCurrentDateTime() {
	    java.util.Date today = new java.util.Date();
	    return  new Timestamp(today.getTime());
	}
	
	/////////////////////////////////////////////////////////////////////////
	public Member[] listPremiumMembers() 
	{
		List<Member> members = new ArrayList<Member>(); //arraylist bc dont know size of members returned
		try {
			 ResultSet rs;
			 String sql_query = "SELECT * FROM Person, Membership WHERE Membership.type ='premium' AND Person.email= Membership.email";
			 prepStmt = conn.prepareStatement(sql_query); 
	         rs = prepStmt.executeQuery();
	         
	         if(!rs.isBeforeFirst()){
	        	 System.out.println("Could not find match. Please try again");
	         }
	         while ( rs.next() ) {
	        	 System.out.println("Premium Member: "+rs.getString("email")+">>"+rs.getString("firstName")+">>"+rs.getString("lastName")+">>"+
	        			 rs.getString("address")+">>"+rs.getString("city")+">>"+rs.getString("state")+">>"+rs.getInt("zip")+"; "+
	        			 rs.getInt("memNum")+">>"+rs.getString("type")+">>"+rs.getDouble("fees")+">>"+rs.getInt("outstandingMovies") );
	        	 Member m = new Member();	        	 
	        	 m.setEmail(rs.getString("email"));
	        	 m.setFirst(rs.getString("firstName"));
	        	 m.setLast(rs.getString("lastName"));
	        	 m.setAddress(rs.getString("address"));
	        	 m.setCity(rs.getString("city"));
	        	 m.setState(rs.getString("state"));
	        	 m.setZip(rs.getInt("zip"));
	        	 m.setMemNum(rs.getInt("memNum"));
	        	 m.setType(rs.getString("type"));
	        	 m.setFees(rs.getDouble("fees"));
	        	 m.setOutstandingMovies(rs.getInt("outstandingMovies"));
	        	 
	        	 members.add(m);
	         }
		} catch(SQLException e) {
			e.printStackTrace();
		}		
		return members.toArray(new Member[members.size()]);
	}
	
	public Member[] listSimpleMembers() 
	{
		ArrayList<Member> members = new ArrayList<Member>(); //arraylist bc dont know size of members returned
		try {
			 ResultSet rs;	 
	         String sql_query = "SELECT * FROM Person, Membership WHERE Membership.type ='simple' AND Person.email= Membership.email";
	         prepStmt = conn.prepareStatement(sql_query); 
	         rs = prepStmt.executeQuery();
	         
	         if(!rs.isBeforeFirst()){
	        	 System.out.println("Could not find match. Please try again");
	         }
	         while ( rs.next() ) {
	        	 System.out.println("Simple Member: "+rs.getString("email")+">>"+rs.getString("firstName")+">>"+rs.getString("lastName")+">>"+
	        			 rs.getString("address")+">>"+rs.getString("city")+">>"+rs.getString("state")+">>"+rs.getInt("zip")+"; "+
	        			 rs.getInt("memNum")+">>"+rs.getString("type")+">>"+rs.getDouble("fees")+">>"+rs.getInt("outstandingMovies") );
	        	 Member m = new Member();	        	 
	        	 m.setEmail(rs.getString("email"));
	        	 m.setFirst(rs.getString("firstName"));
	        	 m.setLast(rs.getString("lastName"));
	        	 m.setAddress(rs.getString("address"));
	        	 m.setCity(rs.getString("city"));
	        	 m.setState(rs.getString("state"));
	        	 m.setZip(rs.getInt("zip"));
	        	 m.setMemNum(rs.getInt("memNum"));
	        	 m.setType(rs.getString("type"));
	        	 m.setFees(rs.getDouble("fees"));
	        	 m.setOutstandingMovies(rs.getInt("outstandingMovies"));
	        	 
	        	 members.add(m);
	         }
		} catch(SQLException e) {
			e.printStackTrace();
		}		
		return members.toArray(new Member[members.size()]);
	}
	
	public Member[] listAllMembers() 
	{
		ArrayList<Member> members = new ArrayList<Member>(); //arraylist bc dont know size of members returned
		try {
			 ResultSet rs;
	         String sql_query = "SELECT * FROM Person, Membership WHERE Person.email= Membership.email";
	         prepStmt = conn.prepareStatement(sql_query); 
	         rs = prepStmt.executeQuery();
	         
	         if(!rs.isBeforeFirst()){
	        	 System.out.println("Could not find match. Please try again");
	         }
	         while ( rs.next() ) {
	        	 System.out.println("All Member: "+rs.getString("email")+">>"+rs.getString("firstName")+">>"+rs.getString("lastName")+">>"+
	        			 rs.getString("address")+">>"+rs.getString("city")+">>"+rs.getString("state")+">>"+rs.getInt("zip")+"; "+
	        			 rs.getInt("memNum")+">>"+rs.getString("type")+">>"+rs.getDouble("fees")+">>"+rs.getInt("outstandingMovies") );
	        	 Member m = new Member();	        	 
	        	 m.setEmail(rs.getString("email"));
	        	 m.setFirst(rs.getString("firstName"));
	        	 m.setLast(rs.getString("lastName"));
	        	 m.setAddress(rs.getString("address"));
	        	 m.setCity(rs.getString("city"));
	        	 m.setState(rs.getString("state"));
	        	 m.setZip(rs.getInt("zip"));
	        	 m.setMemNum(rs.getInt("memNum"));
	        	 m.setType(rs.getString("type"));
	        	 m.setFees(rs.getDouble("fees"));
	        	 m.setOutstandingMovies(rs.getInt("outstandingMovies"));
	        	 
	        	 members.add(m);
	         }
		} catch(SQLException e) {
			e.printStackTrace();
		}		
		return members.toArray(new Member[members.size()]);
	}
	
	public Movie[] listMovies() 
	{
		ArrayList<Movie> movies = new ArrayList<Movie>(); //arraylist bc dont know size of movies returned
		try {
	         ResultSet rs;
	         String sql_query = "SELECT * FROM Movies";
			 prepStmt = conn.prepareStatement(sql_query); 
	         rs = prepStmt.executeQuery();
	         
	         if(!rs.isBeforeFirst()){
	        	 System.out.println("Could not find match. Please try again");
	         }
	         while ( rs.next() ) {
	        	 System.out.println("Movies: "+rs.getInt("movieID")+">>"+rs.getString("title")+">>"+rs.getString("banner")+">>"+
	        			 rs.getDate("releaseDate")+">>"+ rs.getDouble("rentalFee")+">>"+rs.getString("category")+">>"+rs.getInt("quantity"));
	        	 Movie m = new Movie();
	        	 m.setMovieID(rs.getInt("movieID"));
	        	 m.setTitle(rs.getString("title"));
	        	 m.setBanner(rs.getString("banner"));
	        	 m.setDate(rs.getDate("releaseDate").toString());
	        	 m.setRentalFee(rs.getDouble("rentalFee"));
	        	 m.setCategory(rs.getString("category"));
	        	 m.setQuantity(rs.getInt("quantity"));
	        	 
	        	 movies.add(m);
	         }
		} catch(SQLException e) {
			e.printStackTrace();
		}		
		return movies.toArray(new Movie[movies.size()]);
	}
	
	public Member[]  searchMember(Member member_info){
		 List<Member> people = new ArrayList<Member>();
		 Member[] peeps;
		 String query;
		 Boolean [] selectedAttributes = new Boolean[6];

	
		try{
		System.out.println("Inside searchMember");
		 String email = member_info.getEmail();
		 String firstName = member_info.getFirst();
		 String lastName = member_info.getLast();
		 String address = member_info.getAddress();
		 String city = member_info.getCity();
		 String state = member_info.getState();
		 int zip = member_info.getZip();
		 int memeberID = member_info.getMemNum();
		 System.out.println("The email is " + email);
		 System.out.println("the first name is "  + firstName);
		 System.out.println("the state is "+ state);
		 //Find out if member id is known. if so use this 
		 
		 if(memeberID != 0)
		 {
			 System.out.println("---Son of A Mofo-----");
				 query ="SELECT Person.email, Person.firstName, Person.lastName,"+
								"Person.address, Person.city, Person.zip,"+
								"Person.state, Membership.memNum"+
								"FROM Person, Membership"+
								"WHERE Membership.memNum = ? AND"+
								"Person.email = Membership.email";
				
				prepStmt.setInt(1, memeberID);
		 }
		 
		 else if(email != null && !email.equalsIgnoreCase(""))
		 {
			  query = "SELECT Person.email, Person.firstName, Person.lastName, "+
				"Person.address, Person.city, Person.zip, "+
				"Person.state, Membership.memNum "+
				"FROM Person, Membership "+
				"WHERE Person.email = ? AND "+
				"Person.email =Membership.email";
			  
			 prepStmt = conn.prepareStatement(query);
			 prepStmt.setString(1, email);
		 }
		 // Otherwise we use this query that we use to place wild card in to the query if the
		 // 
		 
		 else{
			 Boolean isFirstInWhere = new Boolean(true);
			 String wordToAdd;

			 query ="SELECT Person.email, Person.firstName, Person.lastName, "+
				"Person.address, Person.city, Person.zip, "+
				"Person.state, Membership.memNum "+
				"FROM Person, Membership WHERE Person.email = Membership.email ";
			  
			  System.out.println("--Ok I made it here mabey");
		 
			 if(firstName != null && !firstName.equalsIgnoreCase("") )
				{ 
				 System.out.println(isFirstInWhere);
				 query = query + "AND Person.firstName = ? ";
				 selectedAttributes[0] = true;
				 System.out.println("-- first--");
				}
			 else
				 selectedAttributes[0] = false;
				 

			 if(lastName != null && !lastName.equalsIgnoreCase(""))
			 {
//				 query = query +fixTheQureyWithWhereOrAnd(isFirstInWhere) +"Person.last = ? ";
				 query = query + " And Person.lastName = ? ";
				 selectedAttributes[1] = true;
				 System.out.println("-- Last--");
			 }
			 else
				 selectedAttributes[1] = false;

	
			 if(address != null && !address.equalsIgnoreCase(""))
			 {
//				 query = query +fixTheQureyWithWhereOrAnd(isFirstInWhere) +"Person.address = ? ";
				 query = query + " AND Person.address = ? ";
				 selectedAttributes[2] = true;
				 System.out.println("--Adder--");
				 
			 }
			 else
				 selectedAttributes[2] = false;

			 if(city != null && !city.equalsIgnoreCase(""))
			 {
//				 query = query + fixTheQureyWithWhereOrAnd(isFirstInWhere) +"Person.city = ? ";
				 query = query + "And Person.city = ? ";
				 selectedAttributes[3] = true;
				 System.out.println("--city--");

			 }else
				 selectedAttributes[3] = false;

			 if(state != null && !state.equalsIgnoreCase(""))
			 {
//				 query = query + fixTheQureyWithWhereOrAnd(isFirstInWhere) +"Person.state = ? ";
				 query = query + "AND Person.state = ? ";
				 selectedAttributes[4] = true;
				 System.out.println("--state--");
			 }
			 else
				 selectedAttributes[4] = false;

			 if(zip != 0)
			 {
//				 query = query + fixTheQureyWithWhereOrAnd(isFirstInWhere) +"Person.zip = ? ";
				 query = query + "AND Person.zip = ? ";
				 selectedAttributes[5] = true;
				 System.out.println("--zip--");
			 }else
				 selectedAttributes[5] = false;
	
			 System.out.println(query);
			 System.out.println("the array is " + selectedAttributes.length+ " ");
			 prepStmt = conn.prepareStatement(query);
			 System.out.println("--- 1---");
			setPreParedStatmentForMember(prepStmt, selectedAttributes, member_info);
			 System.out.println("--- 2---");
			 
		 	}
		 System.out.println("ok here i am to save the day");
			rs = prepStmt.executeQuery();
			if(rs == null)
				System.out.println("__--NOOOOOOO Its nullll");
			while(rs.next())
			{
				Member thisPerson = new Member();
				thisPerson.setEmail(rs.getString("email"));
				thisPerson.setAddress(rs.getString("address"));
				thisPerson.setCity(rs.getString("city"));
				thisPerson.setFirst(rs.getString("firstName"));
				thisPerson.setLast(rs.getString("lastName"));
				thisPerson.setZip(rs.getInt("zip"));
				thisPerson.setState(rs.getString("state"));
				thisPerson.setMemNum(rs.getInt("memNum"));
				people.add(thisPerson);
				
			}

	 }catch(SQLException e){
			e.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}
	 peeps = new Member [people.size()];
	 peeps =  people.toArray(peeps);
	 return peeps;
	}
	
	
	public Movie[] searchMovie(Movie movie_info)
	{
		 List<Movie> moviesList = new ArrayList<Movie>();
		 Movie[] movies;
		 String query;
		try{
			System.out.println("Inside searchMovie");
			int movieId = movie_info.getMovieID();
			Boolean [] selectedAttributes = new Boolean[6];
			
			// If we have movie ID then we use this query.
			if(movieId != 0)
			{
				System.out.println("Ok here i be with id " + movieId);
				query = "SELECT Movies.movieID, Movies.title, Movies.banner, Movies.releaseDate,"+
						"Movies.rentalFee, Movies.category, Movies.quantity FROM Movies "+
						"WHERE Movies.movieID = ?";
				prepStmt = conn.prepareStatement(query);
				prepStmt.setInt(1, movieId);
				
			}
			else
			{
				 Boolean isFirstInWhere = new Boolean(true);
				 String wordToAdd;

				query = "SELECT Movies.movieID, Movies.title, Movies.banner, Movies.releaseDate,"+
						"Movies.rentalFee, Movies.category, Movies.quantity FROM Movies";
				
				if(movie_info.getTitle() != null && !movie_info.getTitle().equalsIgnoreCase(""))
				{
					query = query + fixTheQureyWithWhereOrAnd(isFirstInWhere) + "Movies.title = ? ";
					//setTitle = true;
					selectedAttributes[0] = true;
				}
					else
						selectedAttributes[0] = false;
				
				if(movie_info.getBanner() != null && !movie_info.getBanner().equalsIgnoreCase(""))
				{
					query = query +fixTheQureyWithWhereOrAnd(isFirstInWhere)+ "Movies.banner = ? ";
					selectedAttributes[1] = true;
				}
				else
					selectedAttributes[1] = false;
				
				if(movie_info.getCategory() != null && !movie_info.getCategory().equalsIgnoreCase(""))
				{
					query = query+fixTheQureyWithWhereOrAnd(isFirstInWhere) + "Movies.category = ? ";
					selectedAttributes[2] = true;
				}	
				else
					selectedAttributes[2] = false;

				if(movie_info.getDate() != null && !movie_info.getDate().equalsIgnoreCase(""))
				{
					query = query +fixTheQureyWithWhereOrAnd(isFirstInWhere) + "Movies.date = ? ";
					selectedAttributes[3] = true;
				}
				else
					selectedAttributes[3] = false;

				if(movie_info.getQuantity() != 0)
				{
					query = query+fixTheQureyWithWhereOrAnd(isFirstInWhere) + "Movies.quantity = ? ";
					selectedAttributes[4] = true;
				}
				else
					selectedAttributes[4] = false;

				if(movie_info.getRentalFee() != 0)
				{
					query = query+fixTheQureyWithWhereOrAnd(isFirstInWhere) + "Movies.rentalFee = ?";
					selectedAttributes[5] = true;
				}
				else
					selectedAttributes[5] = false;

				prepStmt = conn.prepareStatement(query);
				setPreParedStatmentForMovie(prepStmt, selectedAttributes, movie_info);
			}
				rs = prepStmt.executeQuery();
				if(rs == null)
					System.out.println("Dammm somthing if fubar");
				while(rs.next())
				{
					Movie thisMovie = new Movie();
					thisMovie.setTitle(rs.getString("title"));
					System.out.println("here is were i am and this title is " + thisMovie.getTitle());
					thisMovie.setBanner(rs.getString("banner"));
					thisMovie.setCategory(rs.getString("category"));
					thisMovie.setDate(rs.getDate("releaseDate").toString());
					thisMovie.setQuantity(rs.getInt("quantity"));
					thisMovie.setRentalFee(rs.getDouble("rentalFee"));
					thisMovie.setMovieID(rs.getInt("movieID"));
					moviesList.add(thisMovie);
				}
					
			
		}catch(SQLException e){
			e.printStackTrace();
		}
		
//		 peeps = new Member [people.size()];
//		 peeps =  people.toArray(peeps);
//		 return peeps;

		
		movies = new Movie[moviesList.size()];
		movies = moviesList.toArray(movies);
		
		System.out.println("the movie name is " +  movies[0].getTitle());
		return movies;

	}
	
	public void setPreParedStatmentForMovie(PreparedStatement prepStmt, Boolean [] selectedAttributes, Movie movie_info ) throws SQLException{
		int witchQuestionMark = 0;
		for(int i=0; i < selectedAttributes.length; i++)
		{
			if(selectedAttributes[i])
			{
				witchQuestionMark++;
			    if(i == 0)
			    	prepStmt.setString(witchQuestionMark, movie_info.getTitle());
			    else if(i == 1)
			    	prepStmt.setString(witchQuestionMark, movie_info.getBanner());
			    else if(i==2)
			    	prepStmt.setString(witchQuestionMark, movie_info.getCategory());
			    else if(i==3)
			    	prepStmt.setDate(witchQuestionMark, Date.valueOf(movie_info.getDate()));
			    else if(i==4)
			    	prepStmt.setInt(witchQuestionMark, movie_info.getQuantity());
			    else
			    	prepStmt.setDouble(witchQuestionMark, movie_info.getRentalFee());
			}
		}
		
			
		
	}

	public void setPreParedStatmentForMember(PreparedStatement prepStmt, Boolean [] selectedAttributes, Member member_info ) throws SQLException{
		int witchQuestionMark = 0;
		try{
		for(int i=0; i < selectedAttributes.length; i++)
		{
			 System.out.println("--- 3---");
	
			if(selectedAttributes[i])
			{
				witchQuestionMark++;
			    if(i == 0)
			    	prepStmt.setString(witchQuestionMark, member_info.getFirst());
			    else if(i == 1)
			    	prepStmt.setString(witchQuestionMark, member_info.getLast());
			    else if(i==2)
			    	prepStmt.setString(witchQuestionMark, member_info.getAddress());
			    else if(i==3)
			    	prepStmt.setString(witchQuestionMark, member_info.getCity());
			    else if(i==4)
			    	prepStmt.setString(witchQuestionMark, member_info.getState());
			    else
			    	prepStmt.setInt(witchQuestionMark, member_info.getZip());
			}
			
		}
		}catch(Exception e)
		{
			e.printStackTrace();
			
		}
	}

	public String fixTheQureyWithWhereOrAnd(Boolean firstIn)
	{
		String whatToAdd;
		if(firstIn)
		{
			whatToAdd = " WHERE ";
			firstIn = false;
		}
		else
			whatToAdd = " AND ";
		
		return whatToAdd;
	}
}
